/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.*;
import UTIL.*;
import java.sql.CallableStatement;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;

/**
 *
 * @author Huong Giang
 */
public class RoomDAL {
    public static boolean updateRoom(Room emp) throws Exception {
        boolean result = true;
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_UpdateRoom0(?,?)}");
            cs.setInt("ma", emp.getMaPhong());
            cs.setInt("gia",emp.getGia() );
            int n = cs.executeUpdate();
            if (n == 0) {
                result = false;
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return result;       
    }
    public static boolean checkRoomByMaPhong(int MaPhong) throws Exception {
        boolean result = true;
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_selectRoom(?)}");
            cs.setInt("ma", MaPhong);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                result = true;
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return result;
    }
     public static ArrayList<Room> searchRoomByMaPhong(int MaPhong) throws Exception {
        ArrayList<Room> list = new ArrayList<Room>();
         boolean result = true;
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_SearchRoomByMaPhong(?)}");
            cs.setInt("ma", MaPhong);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Room emp = new Room();
                emp.setTang(rs.getInt("Tang"));
                emp.setMaPhong(rs.getInt("MaPhong"));
                emp.setPhong(rs.getInt("Phong"));
                emp.setGia(rs.getInt("Gia"));
                emp.setTinhTrang(rs.getBoolean("Tinhtrang"));
                list.add(emp);
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return list;
    }
     
     public static ArrayList<Room> selectRoomall() throws Exception {
       ArrayList<Room> list = new ArrayList<Room>();
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_SelectRoomall()}");
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Room emp = new Room();
                emp.setTang(rs.getInt("Tang"));
                emp.setMaPhong(rs.getInt("MaPhong"));
                emp.setPhong(rs.getInt("Phong"));
                emp.setGia(rs.getInt("Gia"));
                emp.setTinhTrang(rs.getBoolean("Tinhtrang"));
                list.add(emp);
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return list;
    }
     public static int[] SelectMaphong() throws SQLException{
     int[] mp= new int[100];
         try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_Maphongnot()}");
             ResultSet rs = cs.executeQuery();
             int i=0;
             while (rs.next()) {
                
                 mp[i]=rs.getInt(1);
                  i++;
             }
            connect.close();
        } catch (SQLException ex) {
        }
        return mp;
    }

public static int getGia(int ma){
    int result = 0;
    try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_getgia(?)}");
            cs.setInt("ma", ma);
             ResultSet rs = cs.executeQuery();
            if(rs.next()){
             result = rs.getInt(1);
            }
            connect.close();
    }catch (SQLException ex){
    }
    
    return result;
    }    
public static void updatePhongThue(int ma){
    try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_updateThue(?)}");
            cs.setInt("ma", ma);
            ResultSet rs = cs.executeQuery();
            if(rs.next()){
            }
            connect.close();
        } catch (SQLException ex) {
        }
            
}

public static void updatePhongTrong(int ma){
    try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_updateTrong(?)}");
            cs.setInt("ma", ma);
            ResultSet rs = cs.executeQuery();
            if(rs.next()){
            }
            connect.close();
        } catch (SQLException ex) {
        }
            
}

 public static ArrayList<Room> SelectPhongTrong(int tang) throws SQLException{
       ArrayList<Room> list = new ArrayList<Room>();
         try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_timphong(?)}");
            cs.setInt("tang", tang);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Room emp = new Room();
                emp.setTang(rs.getInt("Tang"));
                emp.setMaPhong(rs.getInt("MaPhong"));
                emp.setPhong(rs.getInt("Phong"));
                emp.setGia(rs.getInt("Gia"));
                emp.setTinhTrang(rs.getBoolean("Tinhtrang"));
                list.add(emp);
                
            }connect.close();
        } catch (SQLException ex) {
        }
        return list;
    }
 
 public static ArrayList<Room> SelectPhongSaptra(int tang, Date today) throws SQLException{
       ArrayList<Room> list = new ArrayList<Room>();
         try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_phongTra2(?,?)}");
            cs.setInt("tang", tang);
            cs.setDate("today", today);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Room emp = new Room();
                emp.setTang(rs.getInt("Tang"));
                emp.setMaPhong(rs.getInt("MaPhong"));
                emp.setPhong(rs.getInt("Phong"));
                emp.setGia(rs.getInt("Gia"));
                emp.setNgayTra(rs.getDate("NgayTra"));
                list.add(emp);
            }connect.close();
        } catch (SQLException ex) {
        }
        return list;
    }
}
